package com.client.service;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.auth.model.Account;
import com.client.model.IUserData;
import com.client.model.User;
import com.manage.config.Config;
import com.service.AbstractService;
import com.util.ConfigUtil;
import com.utils.dbcp.ConnectionManager;
import com.utils.dbcp.ModelGenerator;

public class IUserServiceImpl extends AbstractService implements IUserService {

	private Logger logger;
	
	public IUserServiceImpl()
	{
		logger = Logger.getLogger(this.getClass());
		this.dbcp = ConfigUtil.get(com.manage.config.Config.SECTION, Config.KEY_DBCP_NAME);
	}
	@Override
	public List<User> getUsers()
	{
		String sql = "select * from user";
		logger.debug("sql: " + sql);
		ModelGenerator<User> dbcpmg = new ModelGenerator<User>(User.class);
		return dbcpmg.generateList(dbcp, sql);
	}

	@Override
	public List<User> getUserByImsi(String imsi)
	{
		String sql = "select * from user where imsi='" + imsi+"'";
		logger.debug("sql: " + sql);
		ModelGenerator<User> dbcpmg = new ModelGenerator<User>(User.class);
		return dbcpmg.generateList(dbcp, sql);
	}

	@Override
	public User getUser(String guid)
	{
		String sql = "select * from user where guid='" + guid + "'";
		logger.debug("sql: " + sql);
		ModelGenerator<User> dbcpmg = new ModelGenerator<User>(User.class);
		return dbcpmg.generate(dbcp, sql);
	}

	@Override
	public boolean saveUser(Account operator, String type, User user) 
	{
		if (operator == null || operator.getId() == null || operator.getId().length() == 0)
			return false;

		String sql = "";
		if (type.equalsIgnoreCase("insert"))
		{
			user.setId(getTableId("user"));
			sql = 
				"insert into user(id,guid,mobile,imsi,imei,smsc,ua,plugin,channelid,projectid,projectpkgid,mobileid," +
				"platform,systemapp,province,region,networknumber,activationstatus,regtime,lasttime,carrier,version," +
				"simsn,mac,androidid,board,manufacturer,device,product,model,sdk,sdkint,sdkrelease,cpid," +
				"appid,chid) " +
				"values(" + String.valueOf(user.getId()) + ",'" + encodeDb(user.getGuid()) + "','" + 
						user.getMobile() + "','" + user.getImsi() + "','" + user.getImei() + "','" +
						user.getSmsc() + "','" + encodeDb(user.getUa()) + "'," + user.getPlugin() + "," +
						user.getChannelId() + "," + user.getProjectId() + "," + user.getProjectPkgId() + ",'" + 
						user.getMobileId() + "','" + encodeDb(user.getPlatform()) + "'," + user.getSystemapp() + ",'" +
						user.getProvince() + "','" + user.getRegion() + "','" + user.getNetworknumber() + "','" + user.getActivationstatus() + "','" + 
						formatDate(user.getRegTime(), "yyyy-MM-dd HH:mm:ss") + "','" +
						formatDate(user.getLastTime(), "yyyy-MM-dd HH:mm:ss") + "','"+user.getCarrier()+"','"+user.getVersion()+"'" +
						",'"+user.getSimsn()+"','"+user.getMac()+"','"+user.getAndroidId()+"'" +
						",'"+user.getBoard()+"','"+user.getManufacturer()+"','"+user.getDevice()+"'" +
						",'"+user.getProduct()+"','"+user.getModel()+"','"+user.getSdk()+"'" +
						",'"+user.getSdkInt()+"','"+user.getSdkrelease()+"','"+user.getCpid()+"'" +
								",'"+user.getAppId()+"','"+user.getChid()+"')";
		}
		else if (type.equalsIgnoreCase("update"))
		{
			sql = 
				"update user set projectpkgid=" + user.getProjectPkgId() + "," +
				"version='"+user.getVersion()+
				"',lasttime='" + formatDate(user.getLastTime(), "yyyy-MM-dd HH:mm:ss") + 
				"',version='"+user.getVersion()+
				"',networknumber="+user.getNetworknumber()+"" +
				",activationstatus="+user.getActivationstatus()+" " +
				",simsn='"+user.getSimsn()+
				"',mac='"+user.getMac()+
				"',androidid='"+user.getAndroidId()+
				"',board='"+user.getBoard()+
				"',manufacturer='"+user.getManufacturer()+
				"',device='"+user.getDevice()+
				"',product='"+user.getProduct()+
				"',model='"+user.getModel()+
				"',sdk='"+user.getSdk()+
				"',sdkint='"+user.getSdkInt()+
				"',sdkrelease='"+user.getSdkrelease()+
				"' " +
				"where imsi='" + user.getImsi() + "'";
		}
		else if (type.equalsIgnoreCase("delete"))
		{
			sql = "delete from user where id=" + String.valueOf(user.getId());
		}
		else
		{
			sql = "";
		}
		logger.debug("sql: " + sql);
		if (sql.length() > 0)
		{
			if (ConnectionManager.getInstance().execute(dbcp, sql))
			{
				return true;
			}
		}
		return false;
	}
	
	public boolean updateUser(Account operator, User user)
	{
		if (operator == null || operator.getId() == null || operator.getId().length() == 0)
			return false;
		
		String sql = 
				"update user set mobile='" + user.getMobile() + "'," +
				"platform='" + encodeDb(user.getPlatform()) + "'," +
				"systemapp=" + user.getSystemapp() + " " +
				"where id=" + user.getId() + "";
		return ConnectionManager.getInstance().execute(dbcp, sql);
	}
	@Override
	public boolean addUserData( IUserData data) {
		data.setId(getTableId("userdata"));
		String sql = 
				"insert into userdata(id,day,hour,minute,plugin,channel,project,province,region,count,newcount,cpid,appid,chid,operator) " +
				"values(" + data.getId() + "," + data.getDay() + "," + data.getHour() + "," +
						data.getMinute() + "," + data.getPlugin() + "," + data.getChannel() + "," + 
						data.getProject() + ",'" + data.getProvince() + "','" + data.getRegion() + "',1,"+data.getNewcount()+"" +
								",'"+data.getCpid()+"','"+data.getAppid()+"','"+data.getChid()+"','"+data.getOperator()+"')";
		return ConnectionManager.getInstance().execute(dbcp, sql);
	}
	@Override
	public boolean updateUserData(IUserData data) {
		String sql = 
				"update userdata set count=count+1, newcount="+data.getNewcount() +
				" where day=" + data.getDay() + " and hour=" + data.getHour() + 
				" and minute=" + data.getMinute() + " and plugin=" + data.getPlugin() + 
				" and channel=" + data.getChannel() + " and project=" + data.getProject() + 
				" and province='" + data.getProvince() + "' and region='" + data.getRegion() + "'"+
				" and cpid='" + data.getCpid() + "' and appid='" + data.getAppid() + "' " +
				" and chid='" + data.getChid() + "' and operator='" + data.getOperator()+ "'";
		return ConnectionManager.getInstance().execute(dbcp, sql);
	}
	
	@Override
	public List<IUserData> getUserData(Account operator, String date1, String date2,
			int plugin, int channel, int project, String province, String region,
			String dimension,int cpid,String appid) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "hour"))
				gb += "0 ";
			else
				fl.add("hour");
			gb += "hour,";
			
			if (!checkQueryDimension(dimension, "minute"))
				gb += "0 ";
			else
				fl.add("minute");
			gb += "minute,";

			if (!checkQueryDimension(dimension, "plugin"))
				gb += "-1 ";
			else
				fl.add("plugin");
			gb += "plugin,";

			if (!checkQueryDimension(dimension, "channel"))
				gb += "-1 ";
			else
				fl.add("channel");
			gb += "channel,";

			if (!checkQueryDimension(dimension, "project"))
				gb += "-1 ";
			else
				fl.add("project");
			gb += "project,";

			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";

			if (!checkQueryDimension(dimension, "region"))
				gb += "'' ";
			else
				fl.add("region");
			gb += "region,";
			
			if (!checkQueryDimension(dimension, "carrier"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "'' ";
			else
				fl.add("cpid");
			gb += "cpid,";
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appid");
			gb += "appid,";
			
			if (!checkQueryDimension(dimension, "chid"))
				gb += "'' ";
			else
				fl.add("chid");
			gb += "chid,";
						
			sql = "select 0 id," + gb + "count,newcount " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ",sum(count) count,sum(newcount) newcount   from userdata " +
				"where (day>=" + date1.replace("-", "")  + " " +
				"and day<=" + date2.replace("-", "") + ") ";
			if (channel > -1)
				sql += " and channel=" + channel + " ";
//			else
//				sql += " and channel in (select channelid from accountchannel where accountid='" + operator.getId() + "') ";
			if (project > -1)
				sql += " and project=" + project + " ";
			if (province != null && !province.equalsIgnoreCase("ALL"))
				sql += "and province='" + province +"' ";
			if (region != null && !region.equalsIgnoreCase("ALL"))
				sql += "and region='" + region + "' ";
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,hour,minute,plugin,channel,project,province,region";
		}

		if (sql.length() > 0) {
			ModelGenerator<IUserData> mg = new ModelGenerator<IUserData>(
					IUserData.class);
			return mg.generateList(dbcp, sql);
		}
		return null;
	}

}
